package com.core.shared;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @author lxf
 * @date 2018/2/12 15:13
 * To change this template use Editor | File and Code Templates.
 */
public class POIUtils {

    private static final Logger LOGGER = LoggerFactory.getLogger(POIUtils.class);
    private static final String EXCEL_TYPE = ".xlsx";


    /**
     * 合并多个excel
     *
     * @param tempExcelUrl        处理excel的临时目录，需要有该目录的读写权限
     * @param finalExcelName      最终的excel名称
     * @param fileNameList        需要合并的临时excel名称，需要全名：a.xlsx
     * @param isDeleteSourceExcel 是否需要删除临时excel文件
     */
    public static void mergeExcel(String tempExcelUrl, String finalExcelName, List<String> fileNameList, boolean isDeleteSourceExcel) {
        InputStream in = null;
        FileOutputStream out = null;
        try {
            XSSFWorkbook newExcelCreat = new XSSFWorkbook();
            for (String fromExcelName : fileNameList) {
                in = new FileInputStream(tempExcelUrl + File.separator + fromExcelName);
                XSSFWorkbook fromExcel = new XSSFWorkbook(in);
                for (int i = 0; i < fromExcel.getNumberOfSheets(); i++) {
                    XSSFSheet oldSheet = fromExcel.getSheetAt(i);
                    XSSFSheet newSheet;
                    try {
                        newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
                    } catch (IllegalArgumentException e) {
                        String newSheetName = fromExcelName.substring(0, fromExcelName.lastIndexOf(".")) + "-";
                        newSheet = newExcelCreat.createSheet(newSheetName + oldSheet.getSheetName());
                    }
                    POIUtils.copySheet(newExcelCreat, oldSheet, newSheet);
                }
            }
            out = new FileOutputStream(tempExcelUrl + File.separator + finalExcelName + EXCEL_TYPE);
            newExcelCreat.write(out);
            out.flush();
            out.close();
            if (isDeleteSourceExcel) {
                for (String fromExcelName : fileNameList) {
                    File file = new File(tempExcelUrl + File.separator + fromExcelName);
                    if (file.exists()) {
                        file.delete();
                    }
                }
            }
        } catch (IOException e) {
            LOGGER.error("POIUtils.mergeExcel,has an IOException", e);
        } finally {
            try {
                if (in != null) {
                    in.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    private static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {
        int num = fromSheet.getNumMergedRegions();
        for (int i = 0; i < num; i++) {
            toSheet.addMergedRegion(fromSheet.getMergedRegion(i));
        }
    }

    private static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
        XSSFCellStyle newstyle = wb.createCellStyle();
        newstyle.cloneStyleFrom(fromCell.getCellStyle());
        //样式
        toCell.setCellStyle(newstyle);
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
        // 不同数据类型处理
        int fromCellType = fromCell.getCellType();
        toCell.setCellType(fromCellType);
        if (fromCellType == XSSFCell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(fromCell)) {
                toCell.setCellValue(fromCell.getDateCellValue());
            } else {
                toCell.setCellValue(fromCell.getNumericCellValue());
            }
        } else if (fromCellType == XSSFCell.CELL_TYPE_STRING) {
            toCell.setCellValue(fromCell.getRichStringCellValue());
        } else if (fromCellType == XSSFCell.CELL_TYPE_BLANK) {

        } else if (fromCellType == XSSFCell.CELL_TYPE_BOOLEAN) {
            toCell.setCellValue(fromCell.getBooleanCellValue());
        } else if (fromCellType == XSSFCell.CELL_TYPE_ERROR) {
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
        } else if (fromCellType == XSSFCell.CELL_TYPE_FORMULA) {
            toCell.setCellFormula(fromCell.getCellFormula());
        } else {
        }
    }

    private static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow) {
        toRow.setHeight(oldRow.getHeight());
        for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
            XSSFCell tmpCell = (XSSFCell) cellIt.next();
            XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell);
        }
    }

    private static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {
        mergeSheetAllRegion(fromSheet, toSheet);
        //设置列宽
        if (fromSheet != null && fromSheet.getRow(fromSheet.getFirstRowNum()) != null) {
            for (int i = 0; i <= fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum(); i++) {
                toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
            }
            for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
                XSSFRow oldRow = (XSSFRow) rowIt.next();
                XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
                copyRow(wb, oldRow, newRow);
            }
        }
    }


    public static void main(String[] args) {
        String tempExcelUrl = "F:\\excel";
        List<String> fileNameList = new ArrayList<>();
        fileNameList.add("t1.xlsx");
        fileNameList.add("t2.xlsx");
        fileNameList.add("t3.xlsx");
        fileNameList.add("t4.xlsx");
        mergeExcel(tempExcelUrl, "总文件", fileNameList, false);
    }

}
